package gt.edu.usac.sgimodel.util;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import oracle.jbo.JboException;

import oracle.jbo.server.DBTransaction;

import oracle.jdbc.OracleTypes;

public class utils_DB {
    public utils_DB() {
    }
    
    public static ResultSet callStoredFunction(DBTransaction dbTransaction,String stmt, Object[] bindVars) {
             CallableStatement st = null;
             ResultSet resultSet = null;
             
             try {
                 st = dbTransaction.createCallableStatement("begin ?:=" + stmt + ";end;", 0);             
                 st.registerOutParameter(1,OracleTypes.NUMBER);
                 if (bindVars != null) {                           
                     for (int z = 0; z < bindVars.length; z++) {
                         st.setObject(z + 2, bindVars[z]);
                     }
                 }
                 st.execute();
                 resultSet = (ResultSet) st.getObject(1);
             } catch (SQLException e) {
                 throw new JboException(e);
             } finally {
                 if (st != null) {
                     try {
                         st.close();
                     } catch (SQLException e) {
                        e.printStackTrace();
                     }
                 }
             }
             return resultSet;
    }   
    
    public static String callStoredFunction2(DBTransaction dbTransaction,String stmt, Object[] bindVars) {
           CallableStatement st = null;
           String result = null;
           
           try {
               st = dbTransaction.createCallableStatement("begin ?:=" + stmt + ";end;", 0);             
               st.registerOutParameter(1,OracleTypes.NUMBER);
               if (bindVars != null) {                           
                   for (int z = 0; z < bindVars.length; z++) {
                       st.setObject(z + 2, bindVars[z]);
                   }
               }
               st.execute();
               result = st.getString(1);
           } catch (SQLException e) {
               throw new JboException(e);
           } finally {
               if (st != null) {
                   try {
                       st.close();
                   } catch (SQLException e) {
                      e.printStackTrace();
                   }
               }
           }
           return result;
    }
    
    public static ResultSet callStoredFunction3(DBTransaction dbTransaction,String stmt, Object[] bindVars) {
             CallableStatement st = null;
             ResultSet resultSet = null;
             
             try {
                 st = dbTransaction.createCallableStatement("begin ?:=" + stmt + ";end;", 0);             
                 st.registerOutParameter(1,OracleTypes.CURSOR);
                 if (bindVars != null) {                           
                     for (int z = 0; z < bindVars.length; z++) {
                         st.setObject(z + 2, bindVars[z]);
                     }
                 }
                 st.execute();
                 resultSet = (ResultSet) st.getObject(1);
             } catch (SQLException e) {
                 throw new JboException(e);
             } finally {
                 if (st != null) {
                     try {
                         st.close();
                     } catch (SQLException e) {
                        e.printStackTrace();
                     }
                 }
             }
             return resultSet;
    }   

    public static void callStoredProcedure(DBTransaction dbTransaction,String stmt, Object[] bindVars) {
    PreparedStatement st = null;
    try {
    // 1. Create a JDBC PreparedStatement for 
    st = dbTransaction.createPreparedStatement("begin "+stmt+";end;",0);
    if (bindVars != null) {
      // 2. Loop over values for the bind variables passed in, if any
      for (int z = 0; z < bindVars.length; z++) {
        // 3. Set the value of each bind variable in the statement
        st.setObject(z + 1, bindVars[z]);
      }
    }
    // 4. Execute the statement
    st.executeUpdate();
    }
    catch (SQLException e) {
    throw new JboException(e);
    }
    finally {
    if (st != null) {
      try {
        // 5. Close the statement
        st.close();
      }
      catch (SQLException e) {
      
      }
    }
    }
    }
    
}

